
<html><HEAD>
<LINK REL=STYLESHEET HREF="default.css" TYPE="text/css">
<TITLE>
Using the SQL statement trace utility</TITLE>
</HEAD>
<BODY>

<!-- Header -->
<p class="ancestor" align="right"><A HREF="connpbp97.htm">Previous</A>&nbsp;&nbsp;<A HREF="connpbp99.htm" >Next</A>
<!-- End Header -->
<A NAME="CJAJIACJ"></A><h1>Using the SQL statement trace utility</h1>
<A NAME="TI2053"></A><h4>SQL statement tracing</h4>
<A NAME="TI2054"></A><p>A separate database trace
utility lets you add date and time entries to a log file for each
SQL statement issued to the database, along with the syntax of the SQL
statement. By default, this utility saves all log entries to a file
named <i>PBTRSQL.log</i> in the initialization path
directory. You can set the initialization path in the on the General
tab of the System Options dialog box. </p>
<A NAME="TI2055"></A><p>You can also change the log file location and log file name
in the registry or in the DbTrace section of the <i>PB.INI</i> file
in the same way you change the trace output file name for the main
database trace utility (see <A HREF="connpbp97.htm#BABHGIHJ">"INI file settings for DBTrace"</A>):<p><PRE> [DbTrace]</PRE><PRE> SqlTraceFile=c:\myApplication\tracesql.log</PRE></p>
<A NAME="TI2056"></A><p>The registry string for the log file name is SqlTraceFile.
It is located under the <i>HKEY_CURRENT_USER\Software\Sybase\PowerBuilder\11.5\DBTrace</i> key. If
the DbTrace section in the PB.INI file has at least one entry, the
registry value is ignored. The default file name is used only if
both the registry value and the <i>PB.INI</i> value
are not set.</p>
<A NAME="TI2057"></A><p>You start the SQL statement trace utility in PowerScript code
by invoking the driver for the DBMS that you want to use with a
TRS modifier. You set the driver in the DBMS property of a connection
object. For example, for the default SQLCA connection object, if
you wanted to use ODBC with SQL tracing, you would code the following:<p><PRE> SQLCA.DBMS="TRS ODBC"</PRE></p>
<A NAME="TI2058"></A><p>You can start and stop the SQL statement trace utility in
PowerScript in the same way you start and stop the main database
utility: you can start trace logging by setting the DBParm parameter
to "PBTrace=1" and you can stop trace
logging by setting the parameter to "PBTrace=0".</p>
<A NAME="TI2059"></A><p>For more information, see <A HREF="connpbp97.htm#BABCIIGB">"Starting a trace in PowerScript with
the PBTrace parameter"</A>.</p>
<A NAME="TI2060"></A><h4>Server-side timestamps</h4>
<A NAME="TI2061"></A><p>Server-side timestamps can be used instead of client-side
timestamps if the connecting PowerBuilder database driver supports
the DBI_GET_SERVER_TIME command type.
Currently, server-side timestamps are available for the ASE, SYC,
SYJ, and ODBC drivers.</p>
<A NAME="TI2062"></A><p><i>PBTRS115.DLL</i> obtains the date and time
from the server only once during the database connection processing.
Each time a new timestamp needs to be generated, it determines the
number of milliseconds that have transpired since the connection
was established and computes the new server-side date and time by
adding the elapsed interval to the initial connection timestamp
obtained from the server.</p>
<A NAME="TI2063"></A><h4>Log file headers</h4>
<A NAME="TI2064"></A><p>Output to the log file is always appended. For ease of reading,
the <i>PBTRS115.DLL</i> produces a banner inside the
log file each time a new database connection is established. The
banner lists the date and time of the database connection using
the system clock on the client workstation. The DBParms for the
database connection are listed immediately under the banner. If
a server timestamp is used for subsequent entries in the log file,
the statement "Using timestamp from DBMS server" is
entered immediately under the DBParm listings.</p>
<A NAME="TI2065"></A><p>When you are running an application with a database trace
utility, one of the DBParm values should include the DisableBind
parameter. You should set DisableBind to 1. Otherwise the syntax
that is logged in the trace output file will contain parameter markers
instead of human-readable values. </p>
<A NAME="TI2066"></A><p>The following output shows a banner from a trace file that
uses a client-side timestamp in the banner itself, and server-side
timestamps elsewhere:<p><PRE> /*---------------------------------------------------*/<br>/*                 1/10/2007  16:08                  */<br>/*---------------------------------------------------*/<br>(60ec068): CONNECT TO TRS ODBC:  DBPARM=ConnectString='DSN=EAS Demo DB V115;UID=dba;PWD=sql'  SERVER=EASDemoDB115<br>(60ec068): Using timestamp from DBMS server. (1/10/2007  16:08:28.079) <br>(60ec068): PREPARE:  (1/10/2007  16:08:44.513)   SELECT DISTINCT  "pbcattbl"."pbt_tnam" , "pbcattbl"."pbt_cmnt"     FROM "pbcattbl"   ORDER BY "pbcattbl"."pbt_tnam"    ASC </PRE></p>

